/**
 * Copyright (c) 2009-2016 https://github.com/zhanglongjian
 * Licensed under the Apache License, Version 2.0 (the "License");
 */
package com.ggy.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.alibaba.druid.pool.DruidDataSource;

/**
 * <p>
 * JDBC辅助工具类
 * </p>
 * <p>
 * @createBy zhang longjian
 * </p>
 * <p>
 * @createTime 2017年7月28日 下午4:19:38
 * </p>
 * <p>
 * TODO
 * </p>
 */
public class JdbcHelper {
	private static DruidDataSource source = null;
	private static PropertiesUtil proUtil = null;
	private Connection conn = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;
	private Statement st = null;

	static {
		proUtil = new PropertiesUtil("db.properties");
		source = new DruidDataSource();
		source.setDriverClassName(proUtil.getValue("driver"));
		source.setUrl(proUtil.getValue("url"));
		source.setUsername(proUtil.getValue("username"));
		source.setPassword(proUtil.getValue("password"));
	}

	public JdbcHelper() {

	}

	/**
	 * 释放资源
	 */
	public void relese() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (st != null) {
				st.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		return source.getConnection();
	}

	/**
	 * 更新操作(insert,update,delete)
	 * 
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数列表
	 * @return
	 */
	public int update(String sql, Object... params) {
		int returnValue = 0;
		try {
			conn = getConnection();
			pst = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pst.setObject(i + 1, params[i]);
				}
			}
			returnValue = pst.executeUpdate();
			Log4jUtil.info("sql statemet:" + sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return returnValue;
	}

	/**
	 * 查询操作 select
	 * 
	 * @param sql
	 *            sql语句
	 * @param params
	 *            查询参数列表
	 * @return
	 */
	public ResultSet query(String sql, Object... params) {
		try {
			conn = getConnection();
			pst = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pst.setObject(i + 1, params[i]);
				}
			}
			rs = pst.executeQuery();
			Log4jUtil.info("sql statemet:" + sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * @param sqls
	 * @return事务执行多条SQL更新语句
	 */
	public boolean updateByTran(String[] sqls) {
		int resu = 0;
		try {
			conn = getConnection();
			// 连接对象默认情况下是自动提交事务，需要设置不自动提交事务
			conn.setAutoCommit(false);
			for (int i = 0; i < sqls.length; i++) {
				pst = conn.prepareStatement(sqls[i]);
				pst.executeUpdate();
			}
			// 提交事务
			conn.commit();
			resu = 1;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			// 回滚事务
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			this.relese();
		}
		return resu > 0;
	}

	public static void main(String[] args) throws SQLException {
		JdbcHelper util = new JdbcHelper();
		ResultSet rs = util.query("select * from sys_res");
		while (rs.next()) {
			System.out.println(rs.getString("res_title"));
		}
	}

}
